<?php

/**
 * CommodityModel
 *
 * @author Nguyen Hai Ha
 * @version 1.0
 */

class CommodityModel {
	/**
	 * The default table name
	 */
	protected $_name = 'eway_commodity';
	protected $_dbAdapter;

	function CommodityModel() {
		$this->_dbAdapter = Zend_Registry::get ( Constant::DB_ADAPTER );
	}

	function getTotalRecords() {
		$sql = "SELECT COUNT(*) FROM " . $this->_name . " WHERE com_banned=0";
		$result = $this->_dbAdapter->fetchCol ( $sql );
		return $result [0];
	}

	public function getAllCommodity( $currentPages, $recordPerPage) {
		$offset = ($currentPages - 1) * $recordPerPage;
		$sql = "SELECT u.usr_fullname name, cate.cat_name cat_name, c.* "
		. "FROM " . $this->_name . " c "
		. "INNER JOIN  eway_user u "
		. "ON c.com_user = u.usr_mobile "
		. "INNER JOIN eway_catcom cat "
		. "ON c.com_id = cat.com_id "
		. "INNER JOIN eway_category cate "
		. "ON cat.cat_id = cate.cat_id "
		. "WHERE c.com_banned=0 "
		. "GROUP BY c.com_id "
		. "ORDER BY c.com_posted_date DESC "
		. "LIMIT $offset,$recordPerPage";
		$result = $this->_dbAdapter->fetchAll ( $sql );
		return $result;
	}

	public function getHotCommodity($count_limit) {
		$sql = "SELECT i.*, c.* FROM eway_commodity c "
		. "LEFT JOIN eway_image i "
		. "ON c.com_id = i.com_id "
		. "WHERE c.com_banned=0 AND c.com_type=1 "
		. "GROUP BY c.com_id "
		. "ORDER BY c.com_hit DESC "
		. "LIMIT $count_limit";
		$result = $this->_dbAdapter->fetchAll ( $sql );
		return $result;
	}

	public function getVipCommodity($count_limit) {
		$sql = "SELECT i.*, c.* FROM eway_commodity c "
		. "LEFT JOIN eway_image i "
		. "ON c.com_id = i.com_id "
		. "WHERE c.com_banned=0 AND c.com_vip=1 AND c.com_type=1 "
		. "GROUP BY c.com_id "
		. "ORDER BY c.com_posted_date DESC "
		. "LIMIT $count_limit";
		$result = $this->_dbAdapter->fetchAll ( $sql );
		return $result;
	}
	
	public function getVipBuyCommodity($count_limit) {
		$sql = "SELECT i.*, c.* FROM eway_commodity c "
		. "LEFT JOIN eway_image i "
		. "ON c.com_id = i.com_id "
		. "WHERE c.com_banned=0 AND c.com_vip=1 AND c.com_type=0 "
		. "GROUP BY c.com_id "
		. "ORDER BY c.com_posted_date DESC "
		. "LIMIT $count_limit";
		$result = $this->_dbAdapter->fetchAll ( $sql );
		return $result;
	}

	public function insertCommodity($commodity = array()) {
		$this->_dbAdapter->insert ( $this->_name, $commodity );
		$id = $this->_dbAdapter->lastInsertId ();
		return $id;
	}

	public function updateCommodity($commodity = array(), $com_id) {
		$where = "com_id=".$com_id;
		$this->_dbAdapter->update($this->_name, $commodity, $where);
	}

	public function getCommodityById($com_id) {
		$sql = "SELECT cat.cat_id, com.* FROM " . $this->_name . " com "
		. "INNER JOIN eway_catcom cat "
		. "ON com.com_id = cat.com_id "
		. "WHERE com.com_id=?";
		$result = $this->_dbAdapter->fetchRow ( $sql, $com_id );
		return $result;
	}
	
	public function getCommodityBySectionId($sec_id, $total_item) {
		$sql = "SELECT com.*, i.* FROM ".$this->_name." com "
		. "LEFT JOIN eway_image i "
		. "ON com.com_id=i.com_id "
		. "INNER JOIN eway_catcom cat "
		. "ON com.com_id=cat.com_id "
		. "WHERE cat.cat_id IN( SELECT cate.cat_id FROM eway_category cate WHERE cate.sec_id=?) "
		. "AND com.com_banned=0 "		
		. "GROUP BY com.com_id "
		. "ORDER BY com.com_posted_date DESC "
		. "LIMIT $total_item";

		$result = $this->_dbAdapter->fetchAll ( $sql, $sec_id );
		return $result;
	}
	
	public function getCommodityByCatId($cat_id, $items) {
		$sql = "SELECT com.* FROM ".$this->_name." com "
		. "INNER JOIN eway_catcom cat "
		. "ON com.com_id=cat.com_id "
		. "WHERE cat.cat_id=? AND com.com_banned=0 "
		. "GROUP BY com.com_id "
		. "ORDER BY com.com_posted_date DESC "
		. "LIMIT $items";

		$result = $this->_dbAdapter->fetchAll ( $sql, $cat_id );
		return $result;
	}
	
	function getTotalRecordsMember($account) {
		$sql = "SELECT COUNT(*) FROM " . $this->_name . " WHERE com_user=? AND com_banned=0 AND com_type=1";
		$result = $this->_dbAdapter->fetchCol ( $sql, $account );
		return $result [0];
	}

	public function getAllCommodityMember($account, $currentPages, $recordPerPage) {
		$offset = ($currentPages - 1) * $recordPerPage;
		$sql = "SELECT u.usr_fullname name, eway_category.cat_name cat_name, c.* FROM " . $this->_name . " c "
		. "INNER JOIN  eway_user u "
		. "ON c.com_user = u.usr_mobile "
		. "INNER JOIN eway_catcom "
		. "ON c.com_id = eway_catcom.com_id "
		. "INNER JOIN eway_category "
		. "ON  eway_catcom.cat_id = eway_category.cat_id "
		. "WHERE c.com_user=?  AND c.com_banned=0  AND c.com_type=1 "
		. "GROUP BY c.com_id "
		. "ORDER BY c.com_posted_date DESC "
		. "LIMIT $offset,$recordPerPage";

		$result = $this->_dbAdapter->fetchAll ( $sql, $account );
		return $result;
	}
	
	function getTotalRecordsMemberBuy($account) {
		$sql = "SELECT COUNT(*) FROM " . $this->_name . " WHERE com_user=? AND com_banned=0 AND com_type=0";
		$result = $this->_dbAdapter->fetchCol ( $sql, $account );
		return $result [0];
	}
	
	public function getAllCommodityMemberBuy($account, $currentPages, $recordPerPage) {
		$offset = ($currentPages - 1) * $recordPerPage;
		$sql = "SELECT u.usr_fullname name, eway_category.cat_name cat_name, c.* FROM " . $this->_name . " c "
		. "INNER JOIN  eway_user u "
		. "ON c.com_user = u.usr_mobile "
		. "INNER JOIN eway_catcom "
		. "ON c.com_id = eway_catcom.com_id "
		. "INNER JOIN eway_category "
		. "ON  eway_catcom.cat_id = eway_category.cat_id "
		. "WHERE c.com_user=?  AND c.com_banned=0 AND c.com_type=0 "
		. "GROUP BY c.com_id "
		. "ORDER BY c.com_posted_date DESC "
		. "LIMIT $offset,$recordPerPage";

		$result = $this->_dbAdapter->fetchAll ( $sql, $account );
		return $result;
	}

	function getTotalRecordsBanned($account) {
		$sql = "SELECT COUNT(*) FROM " . $this->_name . " WHERE com_user=? AND com_banned=1 AND com_type=1";
		$result = $this->_dbAdapter->fetchCol ( $sql, $account );
		return $result [0];
	}

	public function getAllCommodityBanned($account, $currentPages, $recordPerPage) {
		$offset = ($currentPages - 1) * $recordPerPage;
		$sql = "SELECT u.usr_fullname name, eway_category.cat_name cat_name, c.* FROM " . $this->_name . " c "
		. "INNER JOIN  eway_user u "
		. "ON c.com_user = u.usr_mobile "
		. "INNER JOIN eway_catcom "
		. "ON c.com_id = eway_catcom.com_id "
		. "INNER JOIN eway_category "
		. "ON  eway_catcom.cat_id = eway_category.cat_id "
		. "WHERE c.com_user=?  AND c.com_banned=1 AND c.com_type=1 "
		. "GROUP BY c.com_id "
		. "ORDER BY c.com_posted_date DESC "
		. "LIMIT $offset,$recordPerPage";

		$result = $this->_dbAdapter->fetchAll ( $sql, $account );
		return $result;
	}
	
	function getTotalRecordsBannedBuy($account) {
		$sql = "SELECT COUNT(*) FROM " . $this->_name . " WHERE com_user=? AND com_banned=1 AND com_type=0";
		$result = $this->_dbAdapter->fetchCol ( $sql, $account );
		return $result [0];
	}

	public function getAllCommodityBannedBuy($account, $currentPages, $recordPerPage) {
		$offset = ($currentPages - 1) * $recordPerPage;
		$sql = "SELECT u.usr_fullname name, eway_category.cat_name cat_name, c.* FROM " . $this->_name . " c "
		. "INNER JOIN  eway_user u "
		. "ON c.com_user = u.usr_mobile "
		. "INNER JOIN eway_catcom "
		. "ON c.com_id = eway_catcom.com_id "
		. "INNER JOIN eway_category "
		. "ON  eway_catcom.cat_id = eway_category.cat_id "
		. "WHERE c.com_user=?  AND c.com_banned=1 AND c.com_type=0 "
		. "GROUP BY c.com_id "
		. "ORDER BY c.com_posted_date DESC "
		. "LIMIT $offset,$recordPerPage";

		$result = $this->_dbAdapter->fetchAll ( $sql, $account );
		return $result;
	}
}
